package com.company.ems.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class InitAssignmentDatabase {
    
    public static void main(String[] args) {
        System.out.println("开始初始化作业数据库...");
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DatabaseUtil.getConnection();
            
            // 创建用户表
            String createUsersTable = "CREATE TABLE IF NOT EXISTS users (" +
                    "id INT PRIMARY KEY AUTO_INCREMENT, " +
                    "username VARCHAR(50) NOT NULL UNIQUE, " +
                    "password VARCHAR(100) NOT NULL, " +
                    "email VARCHAR(100), " +
                    "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";
            
            pstmt = conn.prepareStatement(createUsersTable);
            pstmt.executeUpdate();
            System.out.println("✓ 用户表创建成功");
            
            // 创建员工表
            String createEmployeesTable = "CREATE TABLE IF NOT EXISTS employees (" +
                    "id INT PRIMARY KEY AUTO_INCREMENT, " +
                    "name VARCHAR(100) NOT NULL, " +
                    "department VARCHAR(50) NOT NULL, " +
                    "position VARCHAR(50) NOT NULL, " +
                    "salary DECIMAL(10,2) NOT NULL, " +
                    "email VARCHAR(100), " +
                    "phone VARCHAR(20), " +
                    "hire_date DATE NOT NULL, " +
                    "created_by INT, " +
                    "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, " +
                    "updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
                    "FOREIGN KEY (created_by) REFERENCES users(id))";
            
            pstmt = conn.prepareStatement(createEmployeesTable);
            pstmt.executeUpdate();
            System.out.println("✓ 员工表创建成功");
            
            // 插入用户数据
            String insertUsers = "INSERT INTO users (username, password, email) VALUES " +
                    "('admin', 'admin123', 'admin@company.com'), " +
                    "('manager', 'manager123', 'manager@company.com') " +
                    "ON DUPLICATE KEY UPDATE username = username";
            
            pstmt = conn.prepareStatement(insertUsers);
            int userRows = pstmt.executeUpdate();
            System.out.println("✓ 用户数据插入完成，影响行数: " + userRows);
            
            // 插入员工数据
            String insertEmployees = "INSERT INTO employees (name, department, position, salary, email, phone, hire_date, created_by) VALUES " +
                    "('张三', '技术部', '软件工程师', 15000.00, 'zhangsan@company.com', '13800138001', '2022-01-15', 1), " +
                    "('李四', '市场部', '市场专员', 12000.00, 'lisi@company.com', '13800138002', '2022-03-20', 1), " +
                    "('王五', '人事部', 'HR经理', 18000.00, 'wangwu@company.com', '13800138003', '2021-11-10', 1) " +
                    "ON DUPLICATE KEY UPDATE name = name";
            
            pstmt = conn.prepareStatement(insertEmployees);
            int employeeRows = pstmt.executeUpdate();
            System.out.println("✓ 员工数据插入完成，影响行数: " + employeeRows);
            
            System.out.println("\n数据库初始化完成！");
            System.out.println("默认账户:");
            System.out.println("- 用户名: admin, 密码: admin123");
            System.out.println("- 用户名: manager, 密码: manager123");
            
        } catch (SQLException e) {
            System.err.println("✗ 数据库操作异常: " + e.getMessage());
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeConnection(conn, pstmt);
        }
    }
}